package com.cdb.service;


import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.io.*;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;

@Service
public class DataModelService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Autowired
    @Qualifier("aliDataSource")
    private DataSource aliDataSource;

    @Autowired
    @Qualifier("primaryJdbcTemplate")
    private JdbcTemplate primaryJdbcTemplate;

    @Autowired
    @Qualifier("aliJdbcTemplate")
    private JdbcTemplate aliJdbcTemplate;

    public void exportTableStructureToExcel() {
        try {
            Workbook workbook = new XSSFWorkbook(); // 创建一个Excel Workbook
            Sheet sheet = workbook.createSheet("Table Structure"); // 创建一个Sheet

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("表名");
            headerRow.createCell(1).setCellValue("字段名");
            headerRow.createCell(2).setCellValue("类型");
            headerRow.createCell(3).setCellValue("长度");

            int rowNum = 1;

            // 获取数据库元数据
            DatabaseMetaData metaData = primaryJdbcTemplate.getDataSource().getConnection().getMetaData();

            // 查询所有的表
            String[] tableTypes = {"TABLE"};
            ResultSet tables = metaData.getTables(null, null, "%", tableTypes);

            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");

                // 查询当前表的列
                ResultSet columns = metaData.getColumns(null, null, tableName, "%");

                while (columns.next()) {
                    Row row = sheet.createRow(rowNum++);
                    row.createCell(0).setCellValue(tableName);
                    row.createCell(1).setCellValue(columns.getString("COLUMN_NAME"));
                    row.createCell(2).setCellValue(columns.getString("TYPE_NAME"));

                    // COLUMN_SIZE可能不适用于所有数据类型
                    // 对于数值类型，你可能想要检查PRECISION
                    int columnSize = columns.getInt("COLUMN_SIZE");
                    if (columnSize != 0) {
                        row.createCell(3).setCellValue(columnSize);
                    }
                }
            }

            String outputFilePath = "D://a.xls";

            // 将Workbook写入文件
            FileOutputStream fileOut = new FileOutputStream(new File(outputFilePath));
            workbook.write(fileOut);

            fileOut.close();
            // 关闭Workbook
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void exportTableToSqlFile() {
        try {
            // 确保你的jdbcTemplate是连接到movie数据库的
            // ...

            // 获取数据库元数据
            DatabaseMetaData metaData = primaryDataSource.getConnection().getMetaData();
            // 查询movie数据库中的所有表
            String[] tableTypes = {"TABLE"};
            ResultSet tables = metaData.getTables(null, "movie", "%", tableTypes); // 注意第二个参数是schema名，这里假设movie是schema名

            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");

                // 注意：不同的数据库系统可能需要不同的查询来获取CREATE TABLE语句
                // 以下是一个通用的MySQL查询
                // String createTableSql = jdbcTemplate.queryForObject(
                //         "SHOW CREATE TABLE `" + tableName + "`", // 使用反引号包围表名
                //         (rs, num) -> rs.getString(2) // 获取CREATE TABLE语句
                // );

                System.out.println(tableName);

                //System.out.println(createTableSql);

                // 将CREATE TABLE语句写入到table_name.sql文件中
                //try (BufferedWriter writer = new BufferedWriter(new FileWriter(tableName + ".sql"))) {
                //    writer.write(createTableSql);
                //} catch (IOException e) {
                //    throw new RuntimeException("Failed to write to SQL file", e);
                //}
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void exportAliTableToSqlFile() {
        try {
            // 确保你的jdbcTemplate是连接到movie数据库的
            // ...

            // 获取数据库元数据
            DatabaseMetaData metaData = aliDataSource.getConnection().getMetaData();
            // 查询movie数据库中的所有表
            String[] tableTypes = {"TABLE"};
            ResultSet tables = metaData.getTables(null, "movie", "%", tableTypes); // 注意第二个参数是schema名，这里假设movie是schema名

            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                System.out.println(tableName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
